# coding:utf-8

import os
import time
from datetime import timedelta, date
from collections import namedtuple
import set_env_path     # 请勿删除

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def opc_day_stat(stat_date):
    """
    created by chenhj at 2017/9/12
    opc日统计
    """
    today = stat_date
    print(today.strftime('%F'))
    start = today
    end = start_date + timedelta(days=1)
    timestamp_range = namedtuple('TimeStamp', 'start end')(
        start.strftime('%s'), end.strftime('%s')
    )
    print(timestamp_range.start)
    print(timestamp_range.end)

    # RO 数
    select_ro_cnt_sql = text("""
        select count(0) as ro_cnt from demand
         where published_at BETWEEN :publish_start and :publish_end
         and deleted_at = 0 and tag = 0 and status in (10, 100, 200)
    """)
    # 抢单人数, 抢单次数
    select_partic_cnt_sql = text("""
        select count(0) as bid_cnt, count(distinct supplier_id) as bid_people_cnt from partic
         where status in (200, 300)
          and created_at BETWEEN :partic_start and :partic_end
          and deleted_at = 0
    """)
    # 付费人数, 付费单数
    select_pay_cnt_sql = text("""
        select count(0) as pay_cnt, count(distinct uid) as pay_people_cnt from bill
        where type = 1 and sub_type = 2 and status = 200
         and created_at BETWEEN :bill_start and :bill_end
         and deleted_at = 0
    """)

    insert_stat_sql = text("""
        insert into opc_day_stat(
          stat_date, ro_cnt, bid_cnt, bid_people_cnt, expired_people_cnt,
           expired_pay_cnt, pay_people_cnt, pay_cnt, created_at
        ) values(
          :stat_date, :ro_cnt, :bid_cnt, :bid_people_cnt, :expired_people_cnt,
           :expired_pay_cnt, :pay_people_cnt, :pay_cnt, :created_at
        )
    """)

    ro_cnt_res = session.execute(
        select_ro_cnt_sql,
        {
            'publish_start': timestamp_range.start,
            'publish_end': timestamp_range.end
        }
    ).fetchone()

    partic_cnt_res = session.execute(
        select_partic_cnt_sql,
        {
            'partic_start': timestamp_range.start,
            'partic_end': timestamp_range.end
        }
    ).fetchone()

    pay_cnt_res = session.execute(
        select_pay_cnt_sql,
        {
            'bill_start': timestamp_range.start,
            'bill_end': timestamp_range.end
        }
    ).fetchone()

    session.execute(
        insert_stat_sql,
        {
            'stat_date': today.strftime('%F'),
            'ro_cnt': ro_cnt_res.ro_cnt,
            'bid_cnt': partic_cnt_res.bid_cnt,
            'bid_people_cnt': partic_cnt_res.bid_people_cnt,
            'expired_people_cnt': 0,
            'expired_pay_cnt': 0,
            'pay_people_cnt': pay_cnt_res.pay_people_cnt,
            'pay_cnt': pay_cnt_res.pay_cnt,
            'created_at': time.time()
        }
    )

if __name__ == '__main__':
    start_date = date(2016, 9, 8)
    end_date = date(2017, 9, 14)
    while start_date < end_date:
        opc_day_stat(start_date)
        start_date += timedelta(days=1)
    session.commit()
    session.close()

